package net.lunjin.dao.impl;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import net.lunjin.bean.Organizations;
import net.lunjin.dao.OrganizationsDao;
import finaler.util.Page;
import finaler.util.PageList;
import finaler.util.SQLUtil;

public class OrganizationsDaoImpl extends MiddleAbstractDao<Organizations> implements OrganizationsDao {
	
	@Override
	protected Class<Organizations> getEntityClass() {
		return Organizations.class;
	}
	
	@Override
	public PageList getOrgList(Page page) throws Exception {
		StringBuffer whereSql = new StringBuffer();
		whereSql.append(" where a.auditFlag = 1");
		if(page.getParameter("comin")!=null && page.getParameter("comin").equals("")==false ){
			whereSql.append(" and a.comin = "+page.getParameter("comin"));
		}
		if(page.getParameter("iscom")!=null && page.getParameter("iscom").equals("")==false ){
			whereSql.append(" and a.iscom = "+page.getParameter("iscom"));
		}
		if(page.getParameter("able")!=null && page.getParameter("able").equals("")==false ){
			whereSql.append(" and a.able = "+page.getParameter("able"));
		}
		if(page.getParameter("combg")!=null && page.getParameter("combg").equals("")==false ){
			whereSql.append(" and a.combg = "+page.getParameter("combg"));
		}
		if(page.getParameter("secu")!=null && page.getParameter("secu").equals("")==false ){
			whereSql.append(" and a.secu = "+page.getParameter("secu"));
		}
		if(page.getParameter("speed")!=null && page.getParameter("speed").equals("")==false ){
			whereSql.append(" and a.speed = "+page.getParameter("speed"));
		}
		if(page.getParameter("foruser")!=null && page.getParameter("foruser").equals("")==false ){
			whereSql.append(" and a.foruser = "+page.getParameter("foruser"));
		}
		if(page.getParameter("network")!=null && page.getParameter("network").equals("")==false ){
			whereSql.append(" and a.network = "+page.getParameter("network"));
		}
		if(page.getParameter("searchkey")!=null && page.getParameter("searchkey").equals("")==false){
			whereSql.append(" and (a.orgname like '%"+page.getParameter("searchkey").toString().trim()+"%' or a.orgshortname like'%"+page.getParameter("searchkey").toString().trim()+"%' )");
		}
		if(page.getParameter("orgvip")!=null && page.getParameter("orgvip").equals("")==false){
			whereSql.append(" and a.orgvip = "+page.getParameter("orgvip").toString().trim() );
		}
		PageList pageList = new PageList(page);//固定
		StringBuffer sqlCount = new StringBuffer();
		sqlCount.append("select count(1) from organizations a ");
		sqlCount.append( whereSql );
		int dataCount = super.getIntBySql( sqlCount.toString() );//取总
		pageList.setTotal(dataCount);//总数
		
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select * from ( ");
		queryListSql.append(" select z.*,round((z.avgycoin+z.avgdcoin+z.avgrcoin+z.avgkcoin)/4) as avgzcoin  from (");
		queryListSql.append(" select a.id,a.orgname,a.orgshortname,a.comin,a.combg,a.whencreate,");
		queryListSql.append(" a.iscom,a.able,a.secu,a.speed,a.foruser,a.orgvip,");
		queryListSql.append(" b.*");
		queryListSql.append(" from  organizations a ");
		queryListSql.append(" left join ");
		queryListSql.append(" (select orgid,count(orgid) as rmcount,");
		queryListSql.append(" round(avg(ycoin)) as avgycoin,");
		queryListSql.append(" round(avg(dcoin)) as avgdcoin,");
		queryListSql.append(" round(avg(rcoin)) as avgrcoin,");
		queryListSql.append(" round(avg(kcoin)) as avgkcoin ");
		queryListSql.append(" from remark group by orgid) b ");
		queryListSql.append(" on  a.id=b.orgid  " );
		queryListSql.append( whereSql );
		queryListSql.append(" ) z" );
		queryListSql.append(" ) y" );
		queryListSql.append(" order by " );
		queryListSql.append(" y.orgvip desc," );//vip
		queryListSql.append(" y.avgzcoin desc," );//排分
		queryListSql.append(" y.rmcount desc" );//评论数
		
		SQLUtil pageSQL = new SQLUtil(queryListSql);//固定
		pageSQL.setFirstResult( page.getStart() );//固定
		pageSQL.setMaxResults( page.getRange() );//固定
		List<Map<String, Object>> dataList = super.findBySqlToMapList(pageSQL.getSql());//数据集
		pageList.setList(dataList);
		return pageList;
	}

	@Override
	public PageList getRecommendOrgList(Page page) throws Exception {
		StringBuffer whereSql = new StringBuffer();
		whereSql.append(" where a.auditFlag = 1 and orgvip = 1");

		PageList pageList = new PageList(page);//固定
		StringBuffer sqlCount = new StringBuffer();
		sqlCount.append("select count(1) from organizations a ");
		sqlCount.append( whereSql );
		int dataCount = super.getIntBySql( sqlCount.toString() );//取总
		pageList.setTotal(dataCount);//总数
		
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select * from ( ");
		queryListSql.append(" select z.*,round((z.avgycoin+z.avgdcoin+z.avgrcoin+z.avgkcoin)/4) as avgzcoin  from (");
		queryListSql.append(" select a.id,a.orgname,a.orgshortname,a.comin,a.combg,a.whencreate,");
		queryListSql.append(" a.iscom,a.able,a.secu,a.speed,a.foruser,a.orgvip,a.web,");
		queryListSql.append(" b.*");
		queryListSql.append(" from  organizations a ");
		queryListSql.append(" left join ");
		queryListSql.append(" (select orgid,count(orgid) as rmcount,");
		queryListSql.append(" round(avg(ycoin)) as avgycoin,");
		queryListSql.append(" round(avg(dcoin)) as avgdcoin,");
		queryListSql.append(" round(avg(rcoin)) as avgrcoin,");
		queryListSql.append(" round(avg(kcoin)) as avgkcoin ");
		queryListSql.append(" from remark group by orgid) b ");
		queryListSql.append(" on  a.id=b.orgid  " );
		queryListSql.append( whereSql );
		queryListSql.append(" ) z " );
		queryListSql.append(" ) y " );
		queryListSql.append(" order by " );
		queryListSql.append(" y.orgvip desc," );
		queryListSql.append(" y.avgzcoin desc," );
		queryListSql.append(" y.rmcount desc" );
		queryListSql.append(" limit 50" );
		
		//SQLUtil pageSQL = new SQLUtil(queryListSql);//固定
		//pageSQL.setFirstResult( page.getStart() );//固定
		//pageSQL.setMaxResults( page.getRange() );//固定
		List<Map<String, Object>> dataList = super.findBySqlToMapList( queryListSql.toString() );//数据集
		pageList.setList(dataList);
		return pageList;
	}

	@Override
	public PageList getHighscoreOrgList(Page page) throws Exception {
		StringBuffer whereSql = new StringBuffer();
		whereSql.append(" select c.*,round((c.avgycoin+c.avgdcoin+c.avgrcoin+c.avgkcoin)/4) as avgzcoin   ");
		whereSql.append(" from (");
		whereSql.append(" select a.id,a.orgname,a.orgshortname,a.comin,a.combg,a.whencreate,");
		whereSql.append(" a.iscom,a.able,a.secu,a.speed,a.foruser,a.auditFlag,a.orgvip,b.*");
		whereSql.append(" from  organizations a");
		whereSql.append(" left join");
		whereSql.append(" (select orgid,count(orgid) as rmcount,");
		whereSql.append(" round(avg(ycoin)) as avgycoin,");
		whereSql.append(" round(avg(dcoin)) as avgdcoin,");
		whereSql.append(" round(avg(rcoin)) as avgrcoin,");
		whereSql.append(" round(avg(kcoin)) as avgkcoin ");
		whereSql.append(" from remark group by orgid) b ");
		whereSql.append(" on  a.id=b.orgid  " );
		whereSql.append(" ) c  " );
		whereSql.append(" ) z where z.auditFlag = 1 and (z.orgvip =1 or z.avgzcoin > 70)");

		PageList pageList = new PageList(page);//固定
		StringBuffer sqlCount = new StringBuffer();
		sqlCount.append("select case when count(1)>50 then 50 else count(1) end from (");
		sqlCount.append( whereSql );
		int dataCount = super.getIntBySql( sqlCount.toString() );//取总
		pageList.setTotal(dataCount);//总数
		
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select * from  ( ");
		queryListSql.append( whereSql );
		queryListSql.append("  order by  orgvip desc, avgzcoin desc  limit 50" );
		//SQLUtil pageSQL = new SQLUtil(queryListSql);//固定
		//pageSQL.setFirstResult( page.getStart() );//固定
		//pageSQL.setMaxResults( page.getRange() );//固定
		List<Map<String, Object>> dataList = super.findBySqlToMapList(queryListSql.toString());//数据集
		pageList.setList(dataList);
		return pageList;
	}

	@Override
	public PageList getLowscoreOrgList(Page page) throws Exception {
		StringBuffer whereSql = new StringBuffer();
		whereSql.append(" select c.*,round((c.avgycoin+c.avgdcoin+c.avgrcoin+c.avgkcoin)/4) as avgzcoin   ");
		whereSql.append(" from (");
		whereSql.append(" select a.id,a.orgname,a.orgshortname,a.comin,a.combg,a.whencreate,");
		whereSql.append(" a.iscom,a.able,a.secu,a.speed,a.foruser,a.auditFlag,a.orgvip,b.*");
		whereSql.append(" from  organizations a");
		whereSql.append(" left join");
		whereSql.append(" (select orgid,count(orgid) as rmcount,");
		whereSql.append(" round(avg(ycoin)) as avgycoin,");
		whereSql.append(" round(avg(dcoin)) as avgdcoin,");
		whereSql.append(" round(avg(rcoin)) as avgrcoin,");
		whereSql.append(" round(avg(kcoin)) as avgkcoin ");
		whereSql.append(" from remark group by orgid) b ");
		whereSql.append(" on  a.id=b.orgid  " );
		whereSql.append(" ) c  " );
		whereSql.append(" ) z where z.auditFlag = 1 and orgvip=0 and z.avgzcoin <60");

		PageList pageList = new PageList(page);//固定
		StringBuffer sqlCount = new StringBuffer();
		sqlCount.append("select case when count(1)>50 then 50 else count(1) end from (");
		sqlCount.append( whereSql );
		int dataCount = super.getIntBySql( sqlCount.toString() );//取总
		pageList.setTotal(dataCount);//总数
		
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select * from  ( ");
		queryListSql.append( whereSql );
		queryListSql.append("  order by avgzcoin,rmcount desc limit 50" );
		//SQLUtil pageSQL = new SQLUtil(queryListSql);//固定
		//pageSQL.setFirstResult( page.getStart() );//固定
		//pageSQL.setMaxResults( page.getRange() );//固定
		List<Map<String, Object>> dataList = super.findBySqlToMapList(queryListSql.toString());//数据集
		pageList.setList(dataList);
		return pageList;
	}
	/**
	 * 随机一个
	 */
	@Override
	public Organizations getRandomRecord() throws Exception {
		String sql  = "select id from organizations where orgvip<>1 ORDER BY RAND() LIMIT 1 ";
		return super.findBySqlToBean(sql);
	}

	@Override
	public List<Organizations> getAuditList() throws Exception {
		String sql  = "select * from organizations where auditflag = 0";
		return super.findBySqlToBeanList(sql);
	}

	@Override
	public int getwantSum() throws Exception {
		String sql  = "select sum(want) from organizations where auditflag = 1";
		return super.getIntBySql(sql);
	}

	@Override
	public void autoUpdateHit() throws Exception {
		String sqlString = "update organizations set hit = hit+1";
		super.executeSql(sqlString);
	}

	@Override
	public int getExistNum(BigDecimal orgid, String Orgshortname) throws Exception {
		String sqlString = "select count(1) from  organizations  where (orgname like '%"+Orgshortname+"%' or orgshortname like '%"+Orgshortname+"%')and id<>"+orgid;
		return super.getIntBySql(sqlString);
	}

	@Override
	public List<Organizations> getRandomComList(int limit) throws Exception {
		String sqlString = "select id,orgshortname from  organizations  where web is not null and web<> '' ORDER BY RAND() limit "+limit;
		return super.findBySqlToBeanList(sqlString);
	}

}
